package com.fagu.openerp;

import java.io.File;
import java.io.FileOutputStream;
import java.net.URISyntaxException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Locale;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Inventory {

	private static Logger log = Logger.getLogger(Inventory.class);
	
	private static File file;
	
	private static final String SQL = "SELECT v.id as CODIGO, v.name AS NOMBRE, v.qty_available AS CANTIDAD, v.cost AS COSTO FROM inventory_valuation_v v WHERE v.qty_available>=0";
	
	private static final Locale locale = new Locale("es", "VE");
	
	public static void main(String args[]) throws URISyntaxException {
		// Init log system
		new MyProperties();
		createDocument();
		Mail mail = new Mail("Adjunto el inventario de este mes", file.getName(), file);
		mail.send();
		log.info("Inventory sent successfully!");
	}
	
	public static void createDocument() {
		Workbook book = new HSSFWorkbook();
		
		Sheet sheet = book.createSheet("Hoja 1");
		
		DB db = new DB();
		
		ResultSet rs = db.getData(SQL);
		
		try {
			int n = rs.getMetaData().getColumnCount();
			Row row = null;
			
			int i = 1;
			while (rs.next()) {
				row = sheet.createRow(i++);
				for (int j = 1; j <= n; j++) {
					Cell cell = row.createCell(j);
					if (j==2) {
						cell.setCellValue(rs.getString(j));
					} else if (j==1) {
						cell.setCellValue(rs.getInt(j));
					} else {
						cell.setCellValue(rs.getDouble(j));
					}
				}
			}
			
			row = sheet.createRow(0);
			for (int j = 1; j <= n; j++){
				Cell cell = row.createCell(j);
				cell.setCellValue(rs.getMetaData().getColumnName(j).toUpperCase());
				HSSFCellStyle cellStyle = (HSSFCellStyle) book.createCellStyle();
				HSSFFont font = (HSSFFont) book.createFont();
				font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
				cellStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
				cellStyle.setFont(font);
				cell.setCellStyle(cellStyle);
				sheet.autoSizeColumn(j);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
		
		String tmpDir = System.getProperty("java.io.tmpdir");
		
		Calendar now = Calendar.getInstance();
		
		file = new File(tmpDir + "Inventario_FAGU_" + now.get(Calendar.DAY_OF_MONTH) + "_" +  now.getDisplayName(Calendar.MONTH, Calendar.LONG, locale).toUpperCase() + "_" + now.get(Calendar.YEAR) + ".xls");
		log.info(file);
		
		try {
			FileOutputStream out = new FileOutputStream(file);
			book.write(out);
			out.close();
		} catch (Exception e) {
			log.error(e.getMessage());
		}
	}
	
}
